/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Data;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import BugTracking.Exceptions;
import config.dataServer;
/**
 * Class encargada, a la conexion, y ejecucion de consultas SQL en la base de datos
 * @author MPanuera
 */
public class DataProcess {
	/*Conexion a Base de Datos*/
    private String user;
    private String pass;
    private String dbclass;
    private String serverURL;
    public static int INSERT = 1;
    
    /* Variables de Entorno */
    private Map<String,String> params = new HashMap<String,String>();
    private List<String> lstParams = new ArrayList<String>();
    private List<String> lstValues = new ArrayList<String>();
    //private Pair<String, String> lista;
    public String query;
    private String table;
    private String scheme;
	private String where;
	private String order;
	private String group;
	private String ide_returning;
	private int fields;
    private Connection con;
    private char type;
    private dataServer db;
    /**
     * Ingresa la consulta
     * @param q
     * Cualquier tipo de consulta
     */
    public void setQuery(String q){
        query = q;
    }
    /**
     * Obtener la consulta ingresada
     * @return
     * Retorna la consulta a ejecutarse
     */
    public String getQuery(){
        return query;
    }
    public void begin(String tabla){
        params.clear();
        table = tabla;
    }
    public void begin(){
        params.clear();
    }
    public void setTable(String t){table = t;}
    public void setReturning(String t){ide_returning = t;}
    public void setWhere(String w){where = w;}
    public void setScheme(String w){scheme = w;}
    public void add(String data, String value){
        lstParams.add(data);
        lstValues.add(value);
        //params.put(data, value);
        fields++;
    }
    public void add(String data){
        lstParams.add(data);
        //params.put(data, "");
        fields++;
    }
    
    /**
     * Nos Permite conectarnos a la Base de Datos
     */
    public DataProcess(){
        db = dataServer.getInstance();
        user = db.getUser();
        pass = db.getPass();
        dbclass = db.getDBClass();
        serverURL = db.getServerURL();
        System.out.println(serverURL);
        con=null;
        query=null;
        where="";
        order="";
        group="";
        type='s';
    }
    public dataServer getConexion(){
    	return db;
    }
    /**
     * Tipo de Transaccion
     * @param type
     * insert, update, delete, function, insert-returning, select
     */
    public void setType(String t){
    	if (t.equalsIgnoreCase("insert")){type = 'i';}
    	else if (t.equalsIgnoreCase("update")){	type = 'u';	}
    	else if (t.equalsIgnoreCase("delete")){	type = 'd';	}
    	else if (t.equalsIgnoreCase("function")){type = 'f';}
    	else if (t.equalsIgnoreCase("insert-returning")){type = 'r';}
    	else{type = 's';}
    }
    public void prepare(){
    	Iterator itp =null;
    	Iterator itv =null;
    	String entity=null;
    	entity = table;
    	if (scheme!=null){entity = scheme+"."+entity;}
    	if (type=='i' || type=='r'){
			String columns="", values="";
			query="insert into "+entity+" (";
			itp = lstParams.iterator();
			itv = lstValues.iterator();
			while (itp.hasNext() && itv.hasNext()) {
				columns+=""+itp.next().toString()+",";
				values+="'"+itv.next().toString()+"',";
			}
			columns = columns.substring(0, columns.length()-1);
			values = values.substring(0, values.length()-1);
			query+=columns+") values (" +values+")";
			if (type=='r') query+=" returning " + ide_returning;				
		}
		else if (type=='u'){
			query+="update "+entity+" set ";
			itp = lstParams.iterator();
			itv = lstValues.iterator();
			while (itp.hasNext() && itv.hasNext()) {
				query+=itp.next().toString()+" = ";
				query+="'"+itv.next().toString()+"',";
				//System.out.println(e.getKey() + " " + e.getValue());
			}
			query = query.substring(0, query.length()-1);
			if (!(where.isEmpty())) query+=" where "+where;
		}
		else if (type=='d'){
			//query+="delete "+table+" set ";
			query="";
			itp = lstParams.iterator();
			itv = lstValues.iterator();
			while (itp.hasNext() && itv.hasNext()) {
				query+=" delete from "+entity+" where ";
				query+=itp.next().toString()+" = ";
				query+="'"+itv.next().toString()+"';";
				//System.out.println(e.getKey() + " " + e.getValue());
			}
		}
		else if (type=='f'){
			query="select "+entity+"(";
			itp = lstParams.iterator();
			while (itp.hasNext()) {
				query+=itp.next().toString()+",";
			}
			query = query.substring(0, query.length()-1);
			query+=")";
		}
		else { //type=='s';
			query="select ";
			itp = lstParams.iterator();
			while (itp.hasNext()) {
				query+=itp.next().toString()+",";
			}
			query = query.substring(0, query.length()-1);
			query+=" from "+entity+" ";
			//System.out.println(where);
			if (!(where.isEmpty())) query+=" where "+where;
			if (!(order.isEmpty())) query+=" order by "+order;
			if (!(group.isEmpty())) query+=" group by "+group;
		}
		//System.out.println(query);
    }
    public void prepare(String t){
    	setType(t);
    	prepare();
    }
    /**
     * Ejecuta una sentencia SQL
     * @return
     * Retorna una matriz de String's, si hay una consulta sino retorna null
     * @exception Error Guarda detalles en el log
     */
    public String[][] execute(){
        String [][] data=null;
        if (con==null) init();
        if (con==null) return data;
        if (query==null) prepare();
        ResultSet rs;
        ResultSetMetaData rsMeta;
        Statement stmt = null;
        if (type=='s' || type=='r'){
        	System.out.println("select");
            try {
                stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            } catch (SQLException ex) {
                Exceptions exc = new Exceptions();
                exc.write("Error al realizar consulta: "+ex.getMessage());
            }
            try {
                rs = stmt.executeQuery(query);
                rsMeta = rs.getMetaData();
                int colCount = rsMeta.getColumnCount();
                int rowCount = 0;
                while (rs.next()) {
                    rowCount++;
                }
                //System.out.println(rowCount);
                rs.beforeFirst();
                data = new String[rowCount][colCount];
                for (int i=0;rs.next();i++){
                    for (int j=1;j<=colCount;j++){
                        data[i][j-1]=rs.getString(j);
                    }
                }
                stmt.execute("END");
                stmt.close();
                con.close();
            } catch (SQLException ex) {
                Exceptions exc = new Exceptions();
                exc.write("Error al ejecutar consulta: "+ex.getMessage());
            }
        }
        else{
            try {
                stmt = con.createStatement();
                stmt.executeQuery(query);
                //Cerramos la conexión
                stmt.execute("END");
                stmt.close();
                con.close();
            } catch (SQLException ex) {
                Exceptions exc = new Exceptions();
                exc.write("Error al realizar consulta: "+ex.getMessage());
            }
        }
        
        return data;
    }
    /**
     * Inicializa la conexion, a la base de datos
     * @exception Error Guarda detalles en el log, sobre el error en la conexion
     */
    public void init(){
    	
        try {
            Class.forName(dbclass);
            con = DriverManager.getConnection(serverURL, user, pass);
            } catch( Exception e ){
            Exceptions ex = new Exceptions();
            ex.write("Error al conectarse a la BD: "+e.getMessage());
            }
    }
}
